use master
go
drop database SurveyMaster
go
create database SurveyMaster
go
use SurveyMaster

create table QuestionTypes
(
	id tinyint primary key,
	name nvarchar(20) not null
)
create table Surveys
(
	id bigint identity(1,1) primary key,
	name nvarchar(200) not null,
	active bit not null default(0),
	header nvarchar(max),
	footer nvarchar(max)
)
create table Questions
(
	id bigint identity(1,1) primary key,
	parentId bigint,
	text nvarchar(200),
	typeId tinyint not null references QuestionTypes(id),
	surveyId bigint references Surveys(id),
	columnId bigint default(0),
	mandatory bit,
)

alter table Questions
add foreign key(parentId) references Questions(id)

create table Users
(
	id bigint identity(1,1) primary key,
	login nvarchar(20) unique not null,
	password nvarchar(20) not null,
	role tinyint not null
)

create table SurveySessions
(
	id bigint identity(1,1) primary key,
	surveyId bigint not null,
	userId bigint not null,
	done bit default(0) not null,
	unique(surveyId, userId)
)

create table Answers
(
	id bigint identity(1,1) primary key,
	sessionId bigint references SurveySessions(id),
	questionId bigint references Questions(id),
	value nvarchar(255)
)

go

insert into QuestionTypes values(1, 'checkbox')
insert into QuestionTypes values(2, 'textbox')
insert into QuestionTypes values(3, 'radio')
insert into QuestionTypes values(4, 'header')
insert into QuestionTypes values(5, 'grid')
insert into QuestionTypes values(6, 'gridcolumn')
insert into QuestionTypes values(7, 'textarea')


insert surveys(name) values('first')

-- roles 1-admin 2-user
insert users(login, password, role) values('admin', '1', 1)
insert users(login, password, role) values('user', '1', 2)

